﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;
namespace BenhVien
{
    public partial class FormNhanVien : Form
    {
        private SqlConnection con;
        private System.Data.DataTable dtHSNV = new System.Data.DataTable("tblHoSoNhanVien");
        private System.Data.DataTable dtDSNV = new System.Data.DataTable("tblDanhSachNhanVien");
        private SqlDataAdapter daHSNV = new SqlDataAdapter();
        private SqlDataAdapter daDSNV = new SqlDataAdapter();

        private void Connect()
        {
            try
            {

                con = new SqlConnection("Data Source=192.168.1.200,1433;Server=IWANTWIN-PC;user id=sa;password=123456;database=QLBenhVien; Connection Timeout=15");
                con.Open();

            }
            catch
            {
                MessageBox.Show("Ket noi that bai", "Loi", MessageBoxButtons.OK);
            }
        }
       
        private void disconnect()
        {
            con.Close();
            con.Dispose();
            con = null;
        }
        private void getData()
        {

            SqlCommand command = new SqlCommand();
            command.Connection = con;
            command.CommandType = CommandType.Text;
            command.CommandText = @"Select
MaNV as N'Ma Nhan Vien',
Ho as N'Ho Nhan Vien',
Ten as N'Ten Nhan Vien',
DiaChi as N'Dia Chi',
SoDienThoai as N'So Dien Thoai',
 NgaySinh as N'Ngay Sinh',
(Case GioiTinh When '1' Then N'Nam'
               When '0' Then N'Nu'
               else  N'Chua xac dinh' end) as N'Gioi Tinh',
                            
MaBaoHiem as N'Ma Bao Hiem',
ViTri as N'Chuc Vu',
Luong as N'Luong',
BacLuong as N'Bac Luong',
MaPhong as N'Ma Phong',
SoGioLamTrongTuan as N'So Gio Lam Trong Tuan',
HinhThucTraLuong as N'Hinh Thuc Tra Luong', 
HopDong as N'Hop Dong'
              
            from NhanVien";
            daHSNV.SelectCommand = command;
            daHSNV.Fill(dtHSNV);
            grvHSNV.DataSource = dtHSNV;
            grvDSNV.DataSource = dtHSNV;

        }

        private void binhding()
        {
            textMaNhanVien.DataBindings.Clear();
            textMaNhanVien.DataBindings.Add("Text", grvHSNV.DataSource, "Ma Nhan Vien");
            textHoNhanVien.DataBindings.Clear();
            textHoNhanVien.DataBindings.Add("Text", grvHSNV.DataSource, "Ho Nhan Vien");
            textTenNhanVien.DataBindings.Clear();
            textTenNhanVien.DataBindings.Add("Text", grvHSNV.DataSource, "Ten Nhan Vien");
            textDiaChiNhanVien.DataBindings.Clear();
            textDiaChiNhanVien.DataBindings.Add("Text", grvHSNV.DataSource, "Dia Chi");
            textSoDienThoai.DataBindings.Clear();
            textSoDienThoai.DataBindings.Add("Text", grvHSNV.DataSource, "So Dien Thoai");
            textNgaySinh.DataBindings.Clear();
            textNgaySinh.DataBindings.Add("Text", grvHSNV.DataSource, "Ngay Sinh");
            textGioiTinh.DataBindings.Clear();
            textGioiTinh.DataBindings.Add("Text", grvHSNV.DataSource, "Gioi Tinh");
            textMaBaoHiem.DataBindings.Clear();
            textMaBaoHiem.DataBindings.Add("Text", grvHSNV.DataSource, "Ma Bao Hiem");
            textViTri.DataBindings.Clear();
            textViTri.DataBindings.Add("Text", grvHSNV.DataSource, "Chuc Vu");
            textLuong.DataBindings.Clear();
            textLuong.DataBindings.Add("Text", grvHSNV.DataSource, "Luong");
            textBacLuong.DataBindings.Clear();
            textBacLuong.DataBindings.Add("Text", grvHSNV.DataSource, "Bac Luong");
            textMaPhong.DataBindings.Clear();
            textMaPhong.DataBindings.Add("Text", grvHSNV.DataSource, "Ma Phong");
            textSoGioLamTrongTuan.DataBindings.Clear();
            textSoGioLamTrongTuan.DataBindings.Add("Text", grvHSNV.DataSource, "So Gio Lam Trong Tuan");
            textHinhThucTraLuong.DataBindings.Clear();
            textHinhThucTraLuong.DataBindings.Add("Text", grvHSNV.DataSource, "Hinh Thuc Tra Luong");
            textHopDong.DataBindings.Clear();
            textHopDong.DataBindings.Add("Text", grvHSNV.DataSource, "Hop Dong");

        }

        public FormNhanVien()
        {
            InitializeComponent();
        }

        private void FormNhanVien_Load(object sender, EventArgs e)
        {
            Connect();
            getData();
            binhding();
        }

        private void tabPage1_Click(object sender, EventArgs e)
        {

        }

        private void grvHSNV_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {

        }

        private void button2_Click(object sender, EventArgs e)
        {

        }

        private void tabPage2_Click(object sender, EventArgs e)
        {

        }

        private void buttonDSNhanVien_Click(object sender, EventArgs e)
        {
            dtHSNV.Clear();
            SqlCommand command = new SqlCommand();
            command.Connection = con;
            command.CommandType = CommandType.Text;
            command.CommandText = @"Select 
                                    MaNV as N'Ma Nhan Vien',
                                    Ho as N'Ho Nhan Vien',
                                    Ten as N'Ten Nhan Vien',
                                    DiaChi as N'Dia Chi',
                                    SoDienThoai as N'So Dien Thoai',
                                     NgaySinh as N'Ngay Sinh',
                                    (Case GioiTinh When '1' Then N'Nam'
                                                   When '0' Then N'Nu'
                                                   else  N'Chua xac dinh' end) as N'Gioi Tinh',                          
                                    MaBaoHiem as N'Ma Bao Hiem',
                                    ViTri as N'Chuc Vu',
                                    Luong as N'Luong',
                                    BacLuong as N'Bac Luong',
                                    MaPhong as N'Ma Phong',
                                    SoGioLamTrongTuan as N'So Gio Lam Trong Tuan',
                                    HinhThucTraLuong as N'Hinh Thuc Tra Luong', 
                                    HopDong as N'Hop Dong'
                                    from NhanVien";



            daHSNV.SelectCommand = command;  //SelectCommand hien tai la command vua tao ra
            daHSNV.Fill(dtHSNV);
            if (dtHSNV.Rows.Count > 0)
            {
                grvHSNV.DataSource = dtHSNV;
            }
            else
            {
                MessageBox.Show("Khong tim thay du lieu dac biet", "Loi", MessageBoxButtons.OK);
            }
        
        }

        private void button2_Click_1(object sender, EventArgs e)
        {
            this.Close();
            Dispose();
            
        }

        private void button1_Click_1(object sender, EventArgs e)
        {
            Excel.Application exl = new Excel.Application();
            Excel.Workbook wb = exl.Workbooks.Add(Excel.XlSheetType.xlWorksheet);
            Excel.Worksheet ws = (Excel.Worksheet)exl.ActiveSheet;

            ws.Name = "Thong tin chi tiet";
            exl.Visible = true;

            ws.Cells[1, 1] = "Ma Nhan Vien";
            ws.Cells[1, 2] = "Ho Nhan Vien";
            ws.Cells[1, 3] = "Ten Nhan Vien";
            ws.Cells[1, 4] = "Dia Chi";
            ws.Cells[1, 5] = "So Dien Thoai";

            ws.Cells[1, 6] = "Ngay Sinh";
            ws.Cells[1, 7] = "Gioi Tinh ";
            ws.Cells[1, 8] = "Ma Bao Hiem";
            ws.Cells[1, 9] = "Chuc vu";
            ws.Cells[1, 10] = "Luong ";

            ws.Cells[1, 11] = "Bac Luong";
            ws.Cells[1, 12] = "Ma Phong";
            ws.Cells[1, 13] = "So Gio Lam Trong Tuan";
            ws.Cells[1, 14] = "Hinh thuc";
            ws.Cells[1, 15] = "Hop dong";

            ws.Cells[2, 1] = textMaNhanVien.Text;
            ws.Cells[2, 2] = textHoNhanVien.Text;
            ws.Cells[2, 3] = textTenNhanVien.Text;
            ws.Cells[2, 4] = textDiaChiNhanVien.Text;
            ws.Cells[2, 5] = textSoDienThoai.Text;

            ws.Cells[2, 6] = textNgaySinh.Text;
            ws.Cells[2, 7] = textGioiTinh.Text;
            ws.Cells[2, 8] = textMaBaoHiem.Text;
            ws.Cells[2, 9] = textViTri.Text;
            ws.Cells[2, 10] = textLuong.Text;

            ws.Cells[2, 11] = textBacLuong.Text;
            ws.Cells[2, 12] = textMaPhong.Text;
            ws.Cells[2, 13] = textSoGioLamTrongTuan.Text;
            ws.Cells[2, 14] = textHinhThucTraLuong.Text;
            ws.Cells[2, 15] = textHopDong.Text;
        }

        private void btInDSNV_Click(object sender, EventArgs e)
        {
            SaveFileDialog save = new SaveFileDialog();
            save.Filter = "Excel file *.xls|*.xls";
            if (save.ShowDialog() == DialogResult.OK)
            {

                Excel.Application xlApp;
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;
                object misValue = System.Reflection.Missing.Value;

                xlApp = new Excel.Application();
                xlWorkBook = xlApp.Workbooks.Add(misValue);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                xlWorkSheet.Name = "Danh sach Nhan vien trong phong";
                int i = 0;
                int j = 0;

                for (i = 0; i <= grvDSNV.RowCount - 1; i++)
                {
                    for (j = 0; j <= grvDSNV.ColumnCount - 1; j++)
                    {

                        DataGridViewCell cell = grvDSNV[j, i];
                        xlWorkSheet.Cells[1, j + 1] = grvDSNV.Columns[j].Name;

                        xlWorkSheet.Cells[i + 2, j + 1] = cell.Value;

                    }
                }

                xlWorkBook.SaveAs(save.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();

                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);

                MessageBox.Show("Excel file created ");
            }

        }
        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            this.Close();
            FormChinh f = new FormChinh();
            f.Show();
        }

        private void buttonTimKiem_Click(object sender, EventArgs e)
        {
            dtHSNV.Clear();
            SqlCommand command = new SqlCommand();
            command.Connection = con;
            command.CommandType = CommandType.Text;
            command.CommandText = @"Select 
                                    MaNV as N'Ma Nhan Vien',
                                    Ho as N'Ho Nhan Vien',
                                    Ten as N'Ten Nhan Vien',
                                    DiaChi as N'Dia Chi',
                                    SoDienThoai as N'So Dien Thoai',
                                     NgaySinh as N'Ngay Sinh',
                                    (Case GioiTinh When '1' Then N'Nam'
                                                   When '0' Then N'Nu'
                                                   else  N'Chua xac dinh' end) as N'Gioi Tinh',                          
                                    MaBaoHiem as N'Ma Bao Hiem',
                                    ViTri as N'Chuc Vu',
                                    Luong as N'Luong',
                                    BacLuong as N'Bac Luong',
                                    MaPhong as N'Ma Phong',
                                    SoGioLamTrongTuan as N'So Gio Lam Trong Tuan',
                                    HinhThucTraLuong as N'Hinh Thuc Tra Luong', 
                                    HopDong as N'Hop Dong'
                            from NhanVien
                            Where Ho LIKE '%'+@HoNV+'%' AND Ten LIKE '%'+@TenNV+'%' ";

            command.Parameters.Add("@HoNV", SqlDbType.NVarChar, 50).Value = textTKHoNhanVien.Text;
            command.Parameters.Add("@TenNV", SqlDbType.NVarChar, 50).Value = textTKTenNhanVien.Text;
            daHSNV.SelectCommand = command;  //SelectCommand hien tai la command vua tao ra
            daHSNV.Fill(dtHSNV);
            if (dtHSNV.Rows.Count > 0)
            {
                grvHSNV.DataSource = dtHSNV;
            }
            else
            {
                MessageBox.Show("Dữ liệu của bạn không có trong danh sách !Ban hay nhap lai", "Loi", MessageBoxButtons.OK);
            }
        }

        private void buttonXoa_Click(object sender, EventArgs e)
        {
            textTKHoNhanVien.Clear();
            textTKTenNhanVien.Clear();

        }

        private void btTim_Click(object sender, EventArgs e)
        {
            dtDSNV.Clear();

            SqlCommand command = new SqlCommand();
            command.Connection = con;
            command.CommandType = CommandType.Text;

            command.CommandText = @"Select 
                                                   MaNV as N'Ma Nhan Vien',
                                            Ho as N'Ho Nhan Vien',
                                            Ten as N'Ten Nhan Vien',
                                            DiaChi as N'Dia Chi',
                                            SoDienThoai as N'So Dien Thoai',
                                             NgaySinh as N'Ngay Sinh',
                                            (Case GioiTinh When '1' Then N'Nam'
                                                           When '0' Then N'Nu'
                                                           else  N'Chua xac dinh' end) as N'Gioi Tinh',
                            
                                            MaBaoHiem as N'Ma Bao Hiem',
                                            ViTri as N'Chuc Vu',
                                            Luong as N'Luong',
                                            BacLuong as N'Bac Luong',
                                            MaPhong as N'Ma Phong',
                                            SoGioLamTrongTuan as N'So Gio Lam Trong Tuan',
                                            HinhThucTraLuong as N'Hinh Thuc Tra Luong', 
                                            HopDong as N'Hop Dong'
                                     From NhanVien
                                     Where  MaPhong LIKE @MaPhong ";
            command.Parameters.Add("@MaPhong", SqlDbType.NVarChar, 50).Value = cbMaPhong.Text;
            daDSNV.SelectCommand = command;  //SelectCommand hien tai la command vua tao ra
            daDSNV.Fill(dtDSNV);
            if (dtDSNV.Rows.Count > 0)
            {
                grvDSNV.DataSource = dtDSNV;
            }
            else
            {
                MessageBox.Show("Khong tim thay du lieu dac biet", "Loi", MessageBoxButtons.OK);
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            dtDSNV.Clear();

            SqlCommand command = new SqlCommand();
            command.Connection = con;
            command.CommandType = CommandType.Text;

            command.CommandText = @"Select 
                                                   MaNV as N'Ma Nhan Vien',
                                            Ho as N'Ho Nhan Vien',
                                            Ten as N'Ten Nhan Vien',
                                            DiaChi as N'Dia Chi',
                                            SoDienThoai as N'So Dien Thoai',
                                             NgaySinh as N'Ngay Sinh',
                                            (Case GioiTinh When '1' Then N'Nam'
                                                           When '0' Then N'Nu'
                                                           else  N'Chua xac dinh' end) as N'Gioi Tinh',
                            
                                            MaBaoHiem as N'Ma Bao Hiem',
                                            ViTri as N'Chuc Vu',
                                            Luong as N'Luong',
                                            BacLuong as N'Bac Luong',
                                            MaPhong as N'Ma Phong',
                                            SoGioLamTrongTuan as N'So Gio Lam Trong Tuan',
                                            HinhThucTraLuong as N'Hinh Thuc Tra Luong', 
                                            HopDong as N'Hop Dong'
                                     From NhanVien  ";

            daDSNV.SelectCommand = command;  //SelectCommand hien tai la command vua tao ra
            daDSNV.Fill(dtDSNV);
            if (dtDSNV.Rows.Count > 0)
            {
                grvDSNV.DataSource = dtDSNV;
            }
            else
            {
                MessageBox.Show("Khong tim thay du lieu dac biet", "Loi", MessageBoxButtons.OK);
            }
        }
    }
}
